﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Betop.Lib.Core.Base;
using Betop.Lib.Enums;
using Betop.Lib.Utilities;
using Betop.Lib.Core.Tree;
using Betop.Lib.Data;

namespace Betop.Lib.Core.User
{
    class UserDAL : BaseDAL
    {
        
        public string GetPagerCondition(string name)
        {
            string condition = " ID <>" + Constants.ADMIN_ID;
            if (name != null && name != "")
            {
                condition += " and (Name like @p0 or LoginName like @p0)";
            }
            return condition;
        }

        public void AddDeptName(IEnumerable<UserInfo> list)
        {
            DataTable dept = dbOper.Query("select ID,Name from Dept");
            DataTable deptUser = dbOper.Query("select * from DeptUser");
           

            foreach (UserInfo user in list)
            {
                user.DeptIDs = new List<int>();
                foreach (DataRow dr in deptUser.Select("UserID = " + user.ID))
                {
                    user.DeptIDs.Add((int)dr["DeptID"]);
                }
                string deptIDs = ConvertUtil.ConvertToDbStr(user.DeptIDs);

                foreach (DataRow dr in dept.Select("ID in (" + deptIDs + ")"))
                {
                    user.DeptName += "," + dr["Name"];
                }
                user.DeptName = (user.DeptName ?? "").Trim(',');
            }
        }

        public object GetID(string loginName)
        {
            string sql = "select ID from [user] where LoginName = @p0";
            List<int> ids = dbOper.Query<int>(sql, loginName);

            if (ids.Count > 0)
            {
                return ids[0];
            }
            else
            {
                return null;
            }
        }

        public List<UserInfo> GetList(IEnumerable<int> lstUserID)
        {
            string userIDs = ConvertUtil.ConvertToDbStr(lstUserID);
            string sql = "select * from [User] where ID in (" + userIDs + ")";

            List<UserInfo> users = dbOper.Query<UserInfo>(sql);
            return users;
        }

        public List<UserInfo> GetList(UserStatus status)
        {
            string sql = "select * from [User] where ID != " + Constants.ADMIN_ID + " and Status = @p0";
            List<UserInfo> users = dbOper.Query<UserInfo>(sql, (int)status);
            return users;
        }

        internal List<UserInfo> GetListByRoleIDs(string strRoleIDs)
        {
            if (strRoleIDs.Trim(',') == "")
            {
                return new List<UserInfo>();
            }
            string sql = "select * from [User] where ID in (select UserID from RoleUser where RoleID in (" + strRoleIDs.Trim(',') + ")) and status !=" + (int)UserStatus.停用;
            List<UserInfo> users = dbOper.Query<UserInfo>(sql);
            return users;
        }
       
        public void Stop(IEnumerable<int> lstUserIDs)
        {
            string strUserIDs = ConvertUtil.ConvertToDbStr(lstUserIDs);
            string strSql = "update  [User] set status=" + (int)UserStatus.停用 + " where ID in (" + strUserIDs + ")";
            dbOper.RunSql(strSql);
        }

        public void Startup(IEnumerable<int> lstUserIDs)
        {
            string strUserIDs = ConvertUtil.ConvertToDbStr(lstUserIDs);
            string strSql = "update [User] set status=" + (int)UserStatus.启用 + " where ID in (" + strUserIDs + ")";
            dbOper.RunSql(strSql);
        }

        //public bool Exist(string strLoginName, string strUserID)
        //{
        //    string sql = "select count(*) from [User] where LoginName=@p0";

        //    if (strUserID != null)
        //    {
        //        sql += " and ID !=" + strUserID;
        //    }

        //    int count = (int)dbOper.GetScalar(sql, strLoginName);

        //    return (count == 0) ? false : true;
            
        //}

        public List<int> GetModuleIDs(int userID)
        {
            string sql = "select ModuleID from ModuleUser where UserID = @p0";

            List<int> moduleIDs = dbOper.Query<int>(sql, userID);

            return moduleIDs;
        }

        public void SaveModuleIDs(int userID, List<int> lstModuelIDs)
        {
            string[] sqls = new string[lstModuelIDs.Count() + 1];
            sqls[0] = "delete from ModuleUser where UserID = " + userID;

            for (int i = 0; i < lstModuelIDs.Count; i++)
            {
                sqls[i + 1] = "insert into ModuleUser(ModuleID, UserID) values(" + lstModuelIDs[i] + "," + userID + ")";
            }

            dbOper.RunSql(sqls);    
                
        }


        public List<TreeInfo> GetDeptUserList()
        {
            string strSql = "select ID,[Name],UpperID,convert(bit,0) as ShowCheckBox,Orders from Dept union " +
                          " select -ID,[Name],DeptID, convert(bit,1) as ShowCheckBox,Orders from DeptUser left join [User] on DeptUser.UserID = [User].ID where DeptUser.UserID not in (select ID from [User] where Status = " + (int)UserStatus.停用 + ") and [ID] != " + Constants.ADMIN_ID;
            List<TreeInfo> trees = dbOper.Query<TreeInfo>(strSql);
            return trees;
        }  

        public List<int> GetRoleIDs(int userID)
        {
            string sql = "select RoleID from RoleUser where UserID = @p0";
            List<int> roleIDs = dbOper.Query<int>(sql, userID);

            return roleIDs;
        }


        

        public static List<int> GetDeptIDs(int userID)
        {
            string sql = "select DeptID from DeptUser where UserID = @p0";
            List<int> deptIDs = DBHelper.Instance().Query<int>(sql, userID);
            return deptIDs;
        }

        public void SaveDept(UserInfo info)
        {
            List<int> lstDeptIDs = info.DeptIDs;

            string strSql = "delete from DeptUser where UserID = @p0";
            dbOper.RunSql(strSql, info.ID);
            for (int i = 0; i < lstDeptIDs.Count; i++)
            {
                strSql = "insert into DeptUser(DeptID,UserID) values( @p0, @p1 )";
                dbOper.RunSql(strSql, lstDeptIDs[i], info.ID);
            }
        }

        public int GetStopedUserCount(string condition)
        {
            string strSql = "select count(*) from [user] where " + GetPagerCondition(condition) + " and status =" + (int)UserStatus.停用;
            int count = dbOper.GetScalar<int>(strSql, "%" + condition + "%");
            return count;
        }

        public DataTable GetNoneDeptList()
        {
            string sql = "select * from [user] where id not in (select distinct UserID from DeptUser) and status <> " + (int)UserStatus.停用 + " and ID <> " + Constants.ADMIN_ID;
            DataTable dt = dbOper.Query(sql);
            return dt;
        }

        public void SaveAdmin(UserInfo info)
        {
            string sql = "insert into [user](ID,Name,LoginName,Password,Orders,Status) values(@p0,@p1,@p2,@p3,@p4,@p5)";
            dbOper.RunSql(sql, info.ID, info.Name, info.LoginName, info.Password, info.Orders, info.Status);
        }

        internal List<UserInfo> GetListByDept(List<int> deptIDs)
        {
            string strDeptIDs = ConvertUtil.ConvertToDbStr(deptIDs);
            string sql = "select * from [User] where ID != " + Constants.ADMIN_ID + " and ID in (select UserID from DeptUser where DeptID in (" + strDeptIDs + "))";
            List<UserInfo> users = dbOper.Query<UserInfo>(sql);
            return users;
        }



        internal void MoveDept(List<int> userIDs, int deptID)
        {
            string[] sqls = new string[userIDs.Count + 1];
            sqls[0] = "delete from DeptUser where UserID in (" + ConvertUtil.ConvertToDbStr(userIDs) + ")";
            for (int i = 0; i < userIDs.Count; i++)
            {
                sqls[i + 1] = "insert into DeptUser(DeptID, UserID) values (" + deptID + "," + userIDs[i] + ")";
            }
            dbOper.RunSql(sqls);
        }

        
    }
}
